import sys
from openpyxl import load_workbook
import pandas as pd
import numpy as np
import datetime
import pymysql
import warnings
warnings.filterwarnings("ignore")
import os
import logging
import glob
import warnings
channel_info_table=r'C:\Users\数据处理\Desktop\判断结果.xlsx'
standard_capacity_table=r'D:\临时中转\对比程序\源数据\standard_capacity_table_202402280931.csv'
RTN_dir=r'\\10.14.36.30\cszx\测试中心\02 外部\02 RTN测试数据及报告\001 RTN测试报告'
ORT_dir=r'\\10.14.36.30\cszx\测试中心\02 外部\01 ORT测试数据及报告\001 ORT测试报告'
sct=pd.read_csv(standard_capacity_table)
sct['cell_spec']=sct['cell_spec'].str.replace('Ah','').astype(float)    #容量
sct['test_temperature']=sct['test_condition'].apply(lambda x:int(x.split('-')[0].replace('°C','℃').replace('℃','')))    #温度
sct['test_temperature']=sct['test_condition'].apply(lambda x:int(x.split('-')[0].replace('°C','℃').replace('℃','')))    #温度
sct['charge_discharge_rate']=sct['test_condition'].apply(lambda x: x.split('-')[1].replace('拟合曲线',''))      #倍率
sct2=sct[sct['cell_type']=='SE4'].copy()
sct2['cell_type']='SJ2'
sct=sct._append(sct2)

sct_k=sct.groupby('cell_type').tail(2)[['cell_type','cell_spec','test_temperature','charge_discharge_rate','cycle','st_capacity']].copy()       #计算斜率
sct_k['斜率']=np.nan
for i in range(1,sct_k.shape[0]):
    if sct_k.iloc[i,0]==sct_k.iloc[i-1,0] and sct_k.iloc[i,1]==sct_k.iloc[i-1,1] and sct_k.iloc[i,2]==sct_k.iloc[i-1,2] and sct_k.iloc[i,3]==sct_k.iloc[i-1,3]:
        sct_k.iloc[i,6]=sct_k.iloc[i-1,5]-sct_k.iloc[i,5]   #斜率大于0
sct_k.dropna(inplace=True)
sct_k2=sct_k[['cell_type','cell_spec','test_temperature','charge_discharge_rate','斜率']].copy()
# print(sct_k2)

wb=load_workbook(channel_info_table)
ws=wb['瑞能']

ws.cell(row=1,column=51).value='比对时间'
ws.cell(row=1,column=52).value='比对结果'
ws.cell(row=1,column=53).value='比对结果原因'
for row in range(2,ws.max_row+1):
    try:
        if ws.cell(row=row,column=53).value=='找不到该测试类别对应的循环数据':  #该通道没有判断过
            CP = ws.cell(row=row, column=12).value  # CP简码(型号)
            temp = ws.cell(row=row, column=3).value  # 温度
            capacity = ws.cell(row=row, column=16).value  # 容量
            test_no = ws.cell(row=row, column=27).value  # 测试单号
            ele_code = ws.cell(row=row, column=28).value  # 电芯编码
            charge_rate = ws.cell(row=row, column=31).value  # 充电倍率
            discharge_rate = ws.cell(row=row, column=34).value  # 放电倍率
            if charge_rate != None and discharge_rate != None:  # 充电倍率和放电倍率均不为空
                rate = charge_rate + '/' + discharge_rate  # 充放电倍率
                if CP == None:  # CP简码等于空
                    ws.cell(row=row, column=51).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                    ws.cell(row=row, column=52).value = '无法判断'
                    ws.cell(row=row, column=53).value = 'CP简码为空'
                elif temp == None:  # 温度等于空
                    ws.cell(row=row, column=51).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                    ws.cell(row=row, column=52).value = '无法判断'
                    ws.cell(row=row, column=53).value = '温度为空'
                elif capacity == None:  # 容量等于空
                    ws.cell(row=row, column=51).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                    ws.cell(row=row, column=52).value = '无法判断'
                    ws.cell(row=row, column=53).value = '容量为空'
                elif test_no == None:   #测试单号等于空
                    ws.cell(row=row, column=51).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                    ws.cell(row=row, column=52).value = '无法判断'
                    ws.cell(row=row, column=53).value = '测试单号为空'
                elif ele_code == None:  #电芯编码等于空
                    ws.cell(row=row, column=51).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                    ws.cell(row=row, column=52).value = '无法判断'
                    ws.cell(row=row, column=53).value = '电芯编码为空'
                else:
                    ele_code = str(ele_code).strip(' ')
                    test_no = test_no.strip(' ')
                    # print(f'CP简码{CP},温度{temp},容量{capacity},实际测试类别{test_type},测试单号{test_no},电芯编码{ele_code},充放电倍率{rate}')
                    cond_CP = sct['cell_type'] == CP  # CP简码判定条件
                    cond_capacity = sct['cell_spec'] == capacity  # 容量判定条件
                    cond_temp = sct['test_temperature'] == temp  # 温度判定条件
                    cond_rate = sct['charge_discharge_rate'] == rate  # 倍率判定条件
                    sct3 = sct[cond_CP & cond_capacity & cond_temp & cond_rate].copy()
                    if sct3.shape[0] == 0:
                        ws.cell(row=row, column=51).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                        ws.cell(row=row, column=52).value = '无法判断'
                        ws.cell(row=row, column=53).value = '标准曲线不存在'
                        if sct[cond_CP].shape[0] == 0:
                            ws.cell(row=row, column=53).value = '数据库中此CP简码循环控制函数不存在'
                        elif sct[cond_CP & cond_temp].shape[0] == 0:
                            ws.cell(row=row, column=53).value = '数据库中此CP简码对应该温度循环控制函数不存在'
                        elif sct[cond_CP & cond_temp & cond_rate].shape[0] == 0:
                            ws.cell(row=row, column=53).value = '数据库中此CP简码对应该倍率循环控制函数不存在'
                        else:
                            ws.cell(row=row, column=53).value = '数据库中此CP简码对应该容量循环控制函数不存在'
                    else:  # 标准曲线不等于空
                        year = test_no[:4]  # 年
                        month = test_no[4:6] + '月'  # 月
                        month = month.strip('0')
                        dir_RTN = os.path.join(RTN_dir, year, month)
                        dir_RTN_list = glob.glob(fr"{dir_RTN}\{test_no}*")
                        if len(dir_RTN_list) == 0:  # RTN找不到再去ORT找
                            dir_ORT = os.path.join(ORT_dir, year, month)
                            dir_ORT_list = glob.glob(fr"{dir_ORT}\{test_no}*")
                            # print(dir_ORT_list)
                            if len(dir_ORT_list) != 0:  # 是ORT的单
                                for i in dir_ORT_list:
                                    if i.count('循环')>0 and i.strip(".xlsx").endswith('循环'):     #文件查找
                                        file =i
                                        print(file)
                                        df = pd.read_excel(file, sheet_name=None)
                                        for sheet in df.keys():
                                            if str(temp) in sheet and sheet.strip(" ").endswith('数据'):  #工作簿查找(符合条件则说明该测试单已迁移数据)
                                                df = pd.read_excel(file, sheet_name=sheet)
                                                df2 = df.iloc[2:, 16:].copy()
                                                df2.columns = df2.iloc[0, :]
                                                df3 = df2.iloc[2:, :].copy()
                                                dict1 = {}  # 键是电芯条码,值是容量保持率
                                                for i in range(0, df3.shape[1], 15):
                                                    if type(df3.columns[i]) is float:
                                                        break
                                                    else:
                                                        if ele_code==df3.columns[i]:
                                                            df4 = df3.iloc[:, i + 2].copy()
                                                            df4.dropna(inplace=True)
                                                            df4 = pd.DataFrame(df4)
                                                            df4.columns = ['容量保持率']
                                                            df4.insert(loc=0, column='循环号',value=np.array([i for i in range(1, df4.shape[0] + 1)]))
                                                            if df4.shape[0] < 500:
                                                                ws.cell(row=row,column=51).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                                                                ws.cell(row=row, column=52).value = '正在进行测试'
                                                                ws.cell(row=row, column=53).value = '未跑到500圈以上'
                                                            else:
                                                                if df4.shape[0]<sct3['cycle'].max():
                                                                    print(f"{ele_code}的循环圈数小于拟合曲线最大圈数")
                                                                    sct4 = sct3[['cycle','st_capacity']].copy()
                                                                    fact_standard = pd.merge(left=df4, right=sct4, left_on='循环号',right_on='cycle')
                                                                    del fact_standard['cycle']
                                                                    fact_standard2 = fact_standard.tail(20)  # 比较最新的20圈循环数据
                                                                    fact_standard2['差值'] = fact_standard2['容量保持率'] - fact_standard2['st_capacity']
                                                                    fact_standard3 = fact_standard2[fact_standard2['差值'] < 0].copy()  # 意味着该圈数据低于标准值
                                                                    del fact_standard3['容量保持率'],fact_standard3['st_capacity']
                                                                    if fact_standard3.shape[0] < 5:
                                                                        ws.cell(row=row,column=51).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                                                                        ws.cell(row=row, column=52).value = '合格'
                                                                        ws.cell(row=row, column=53).value = '/'
                                                                    else:
                                                                        fact_standard3['序号'] = np.array([i for i in range(fact_standard3.shape[0])])
                                                                        fact_standard3['cycle_flag'] = fact_standard3['循环号'] -fact_standard3['序号']  # cycle_flag相同的意味着连续
                                                                        for cycle_flag in fact_standard3['cycle_flag'].unique():
                                                                            if list(fact_standard3['cycle_flag']).count(cycle_flag) < 5:
                                                                                fact_standard3.drop(index=fact_standard3[fact_standard3['cycle_flag'] == cycle_flag].index,inplace=True)
                                                                        if fact_standard3.shape[0] == 0:  # 说明不存在连续5圈数据低于标准的情况
                                                                            ws.cell(row=row,column=51).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                                                                            ws.cell(row=row, column=52).value = '合格'
                                                                            ws.cell(row=row, column=53).value = '/'
                                                                        else:
                                                                            num = fact_standard3['循环号'].iloc[0]
                                                                            ws.cell(row=row,column=51).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                                                                            ws.cell(row=row, column=52).value = '不合格'
                                                                            ws.cell(row=row,column=53).value = f'从第{num}圈开始连续5圈低于标准值'
                                                                else:
                                                                    print(f"{ele_code}的循环圈数大于拟合曲线最大圈数")
                                                                    sct_5 = sct3.tail(5).copy()  # 标准部分最新取5圈
                                                                    df_result_5 = df4[(df4['循环号'] >= sct_5['cycle'].min()) & (df4['循环号'] <= sct_5['cycle'].max())].copy()  # 实际循环取5圈对应标准部分的最新5圈
                                                                    df_result2 = pd.merge(left=df_result_5, right=sct_5,left_on='循环号', right_on='cycle')
                                                                    del df_result2['循环号']
                                                                    print(df_result2)
                                                                    df_result3 = df_result2.tail(20).copy()
                                                                    df_result4 = df_result3[['cycle', '容量保持率', 'st_capacity']].copy()
                                                                    df_result4['容量保持率'] = df_result4['容量保持率'].astype(float)
                                                                    df_result4['st_capacity'] =df_result4['st_capacity'].astype(float)
                                                                    df_result4['flag'] = df_result4['容量保持率'] - df_result4['st_capacity']
                                                                    df_result5 = df_result4[df_result4['flag'] < 0].copy()
                                                                    if df_result5.shape[0] == 5:
                                                                        ws.cell(row=i, column=51).value = '标准部分不合格'
                                                                        ws.cell(row=i,column=52).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                                                                        ws.cell(row=i,column=53).value = '实际循环圈数超过标准圈数,且标准部分圈数连续5圈低于标准'
                                                                    else:
                                                                        ws.cell(row=i, column=51).value = '标准部分合格'
                                                                        ws.cell(row=i,column=52).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                                                                        ws.cell(row=i,column=53).value = '实际循环圈数超过标准圈数,标准部分圈数合格'
                                                                    df_result_20 = df4.tail(20).copy()  # 实际循环圈数最新20圈部分
                                                                    sct_20 = pd.DataFrame()
                                                                    sct_20['cycle'] = df_result_20['cycle']
                                                                    cond_brand = sct_k2['cell_type'] == CP
                                                                    cond_capacity = sct_k2['cell_spec'] == capacity
                                                                    cond_temp = sct_k2['test_temperature'] == temp
                                                                    cond_rate = sct_k2['charge_discharge_rate'] == rate
                                                                    df3_k = sct_k2[cond_brand & cond_capacity & cond_temp & cond_rate]
                                                                    k = df3_k['斜率'].iloc[0]
                                                                    print(f'标准曲线斜率为{k}')
                                                                    sct_20['预测容量保持率'] = np.nan
                                                                    last_capacity_fading = list(sct_5['标准容量保持率'])[-1]
                                                                    last_cycle = list(sct_5['cycle'])[-1]
                                                                    for j in range(sct_20.shape[0]):
                                                                        sct_20['预测容量保持率'].iloc[j] = k * (last_cycle-sct_20['cycle'].iloc[j]) + last_capacity_fading  # 计算标准容量保持率预测值(假设标准cycle跑到与实际圈数相一致)
                                                                    df2 = pd.merge(left=df_result_20, left_on='cycle',right=sct_20, right_on='cycle')
                                                                    df3 = df2.tail(20).copy()
                                                                    df4 = df3[['cycle', '容量保持率', '预测容量保持率']].copy()
                                                                    df4['容量保持率'] = df4['容量保持率'].astype(float)
                                                                    df4['预测容量保持率'] = df4['预测容量保持率'].astype(float)
                                                                    df4['flag'] = df4['容量保持率'] - df4['预测容量保持率']
                                                                    df5 = df4[df4['flag'] < 0].copy()
                                                                    if df5.shape[0] < 5:
                                                                        ws.cell(row=i, column=51).value = ws.cell(row=i, column=51).value+',实际循环圈数部分合格'
                                                                        ws.cell(row=i,column=52).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                                                                        ws.cell(row=i, column=53).value = ws.cell(row=i,column=53).value + ',实际循环圈数部分合格'
                                                                    else:
                                                                        for cycle_flag in df5['cycle_flag'].unique():
                                                                            if list(df5['cycle_flag']).count(cycle_flag) < 5:
                                                                                df5.drop(index=df5[df5['cycle_flag'] == cycle_flag].index,inplace=True)
                                                                        if df5.shape[0] == 0:
                                                                            ws.cell(row=i, column=51).value = ws.cell(row=i, column=51).value+',实际循环圈数部分合格'
                                                                            ws.cell(row=i,column=52).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                                                                            ws.cell(row=i, column=53).value = ws.cell(row=i,column=53).value + ',实际循环圈数部分合格'
                                                                        else:
                                                                            num = df5['cycle'].iloc[0]
                                                                            ws.cell(row=i, column=51).value = ws.cell(row=i, column=51).value+',实际循环圈数部分不合格'
                                                                            ws.cell(row=i,column=52).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                                                                            ws.cell(row=i, column=53).value = ws.cell(row=i,column=53).value + f',实际循环圈数最新20圈部分不合格,从第{num}圈开始连续5圈低于标准值'

                            else:   #该通道的测试单号既不是RTN也不是ORT
                                ws.cell(row=row, column=51).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                                ws.cell(row=row, column=52).value = '无法判断'
                                ws.cell(row=row, column=53).value = '无循环数据'
                        else:   # 是RTN的单
                            for i in dir_RTN_list:
                                if i.count('循环') > 0 and i.strip(".xlsx").endswith('循环'):   #文件名查找
                                    file = i
                                    print(file)
                                    df = pd.read_excel(file, sheet_name=None)
                                    for sheet in df.keys():
                                        if str(temp) in sheet and sheet.strip(" ").endswith('数据'):  #工作簿查找(符合条件则说明该测试单已迁移数据)
                                            df = pd.read_excel(file, sheet_name=sheet)
                                            df2 = df.iloc[2:, 16:].copy()
                                            df2.columns = df2.iloc[0, :]
                                            df3 = df2.iloc[2:, :].copy()
                                            dict1 = {}  # 键是电芯条码,值是容量保持率
                                            for i in range(0, df3.shape[1], 15):
                                                if type(df3.columns[i]) is float:
                                                    break
                                                else:
                                                    if ele_code == df3.columns[i]:
                                                        df4 = df3.iloc[:, i + 2].copy()
                                                        df4.dropna(inplace=True)
                                                        df4 = pd.DataFrame(df4)
                                                        df4.columns = ['容量保持率']
                                                        df4.insert(loc=0, column='循环号',value=np.array([i for i in range(1, df4.shape[0] + 1)]))
                                                        if df4.shape[0] < 500:
                                                            ws.cell(row=row,column=51).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                                                            ws.cell(row=row, column=52).value = '正在进行测试'
                                                            ws.cell(row=row, column=53).value = '未跑到500圈以上'
                                                        else:
                                                            if df4.shape[0] < sct3['cycle'].max():
                                                                print(f"{ele_code}的循环圈数小于拟合曲线最大圈数")
                                                                sct4 = sct3[['cycle', 'st_capacity']].copy()
                                                                fact_standard = pd.merge(left=df4, right=sct4,left_on='循环号',right_on='cycle')
                                                                del fact_standard['cycle']
                                                                fact_standard2 = fact_standard.tail(20)  # 比较最新的20圈循环数据
                                                                fact_standard2['差值'] = fact_standard2['容量保持率'] - fact_standard2['st_capacity']
                                                                fact_standard3 = fact_standard2[fact_standard2['差值'] < 0].copy()  # 意味着该圈数据低于标准值
                                                                del fact_standard3['容量保持率'], fact_standard3['st_capacity']
                                                                if fact_standard3.shape[0] < 5:
                                                                    ws.cell(row=row,column=51).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                                                                    ws.cell(row=row, column=52).value = '合格'
                                                                    ws.cell(row=row, column=53).value = '/'
                                                                else:
                                                                    fact_standard3['序号'] = np.array([i for i in range(fact_standard3.shape[0])])
                                                                    fact_standard3['cycle_flag'] = fact_standard3['循环号'] - fact_standard3['序号']  # cycle_flag相同的意味着连续
                                                                    for cycle_flag in fact_standard3['cycle_flag'].unique():
                                                                        if list(fact_standard3['cycle_flag']).count(cycle_flag) < 5:
                                                                            fact_standard3.drop(index=fact_standard3[fact_standard3['cycle_flag'] == cycle_flag].index,inplace=True)
                                                                    if fact_standard3.shape[0] == 0:  # 说明不存在连续5圈数据低于标准的情况
                                                                        ws.cell(row=row,column=51).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                                                                        ws.cell(row=row, column=52).value = '合格'
                                                                        ws.cell(row=row, column=53).value = '/'
                                                                    else:
                                                                        num = fact_standard3['循环号'].iloc[0]
                                                                        ws.cell(row=row,column=51).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                                                                        ws.cell(row=row, column=52).value = '不合格'
                                                                        ws.cell(row=row,column=53).value = f'从第{num}圈开始连续5圈低于标准值'
                                                            else:
                                                                print(f"{ele_code}的循环圈数大于拟合曲线最大圈数")
                                                                sct_5 = sct3.tail(5).copy()  # 标准部分最新取5圈
                                                                df_result_5 = df4[(df4['循环号'] >= sct_5['cycle'].min()) & (df4['循环号'] <= sct_5['cycle'].max())].copy()  # 实际循环取5圈对应标准部分的最新5圈
                                                                df_result2 = pd.merge(left=df_result_5, right=sct_5,left_on='循环号',right_on='cycle')
                                                                del df_result2['循环号']
                                                                print(df_result2)
                                                                df_result3 = df_result2.tail(20).copy()
                                                                df_result4 = df_result3[['cycle', '容量保持率', 'st_capacity']].copy()
                                                                df_result4['容量保持率'] = df_result4['容量保持率'].astype(float)
                                                                df_result4['st_capacity'] = df_result4['st_capacity'].astype(float)
                                                                df_result4['flag'] = df_result4['容量保持率'] - df_result4['st_capacity']
                                                                df_result5 = df_result4[df_result4['flag'] < 0].copy()
                                                                if df_result5.shape[0] == 5:
                                                                    ws.cell(row=i,column=52).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                                                                    ws.cell(row=i,column=53).value = '实际循环圈数超过标准圈数,且标准部分圈数连续5圈低于标准'
                                                                else:
                                                                    ws.cell(row=i,column=52).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                                                                    ws.cell(row=i,column=53).value = '实际循环圈数超过标准圈数,标准部分圈数合格'
                                                                df_result_20 = df4.tail(20).copy()  # 实际循环圈数最新20圈部分
                                                                sct_20 = pd.DataFrame()
                                                                sct_20['cycle'] = df_result_20['cycle']
                                                                cond_brand = sct_k2['cell_type'] == CP
                                                                cond_capacity = sct_k2['cell_spec'] == capacity
                                                                cond_temp = sct_k2['test_temperature'] == temp
                                                                cond_rate = sct_k2['charge_discharge_rate'] == rate
                                                                df3_k = sct_k2[cond_brand & cond_capacity & cond_temp & cond_rate]
                                                                k = df3_k['斜率'].iloc[0]
                                                                print(f'标准曲线斜率为{k}')
                                                                sct_20['预测容量保持率'] = np.nan
                                                                last_capacity_fading = list(sct_5['标准容量保持率'])[-1]
                                                                last_cycle = list(sct_5['cycle'])[-1]
                                                                for j in range(sct_20.shape[0]):
                                                                    sct_20['预测容量保持率'].iloc[j] = k * (last_cycle-sct_20['cycle'].iloc[j]) + last_capacity_fading  # 计算标准容量保持率预测值(假设标准cycle跑到与实际圈数相一致)
                                                                df2 = pd.merge(left=df_result_20, left_on='cycle',right=sct_20, right_on='cycle')
                                                                df3 = df2.tail(20).copy()
                                                                df4 = df3[['cycle', '容量保持率', '预测容量保持率']].copy()
                                                                df4['容量保持率'] = df4['容量保持率'].astype(float)
                                                                df4['预测容量保持率'] = df4['预测容量保持率'].astype(float)
                                                                df4['flag'] = df4['容量保持率'] - df4['预测容量保持率']
                                                                df5 = df4[df4['flag'] < 0].copy()
                                                                if df5.shape[0] < 5:
                                                                    ws.cell(row=i, column=51).value = '合格'
                                                                    ws.cell(row=i,column=52).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                                                                    ws.cell(row=i, column=53).value = ws.cell(row=i,column=53).value + ',实际循环圈数部分合格'
                                                                else:
                                                                    for cycle_flag in df5['cycle_flag'].unique():
                                                                        if list(df5['cycle_flag']).count(cycle_flag) < 5:
                                                                            df5.drop(index=df5[df5['cycle_flag'] == cycle_flag].index,inplace=True)
                                                                    if df5.shape[0] == 0:
                                                                        ws.cell(row=i, column=51).value = '合格'
                                                                        ws.cell(row=i,column=52).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                                                                        ws.cell(row=i, column=53).value = ws.cell(row=i,column=53).value + ',实际循环圈数部分合格'
                                                                    else:
                                                                        num = df5['cycle'].iloc[0]
                                                                        ws.cell(row=i, column=51).value = '不合格'
                                                                        ws.cell(row=i,column=52).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                                                                        ws.cell(row=i, column=53).value = ws.cell(row=i,column=53).value + f',实际循环圈数最新20圈部分不合格,从第{num}圈开始连续5圈低于标准值'
            elif charge_rate == None:
                ws.cell(row=row, column=51).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                ws.cell(row=row, column=52).value = '无法判断'
                ws.cell(row=row, column=53).value = '充电倍率为空'
            else:
                ws.cell(row=row, column=51).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                ws.cell(row=row, column=52).value = '无法判断'
                ws.cell(row=row, column=53).value = '放电倍率为空'
    except Exception as e:
        print(f"发生错误:{e}")
        print('执行的是except')
        wb.save(channel_info_table)
        print('保存成功')
wb.save(channel_info_table)
print('保存成功')







